"""
Case Type   : to_timestamp
Case Name   : to_timestamp结合匿名函数使用
Create At   : 2025/6/17
Owner       : @m0_68540079
Description :
    1、准备数据
    2、结合匿名函数使用
    3、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
History     :
"""
import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

class CastTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.tb_name = 't_tbf_0030'

    def test_to_binary_float(self):
        text = '-----step1:准备数据;expect:成功-----'
        self.log.info(text)
        sql = f'''drop table if exists {self.tb_name} cascade;
        create table {self.tb_name} (time1 timestamp, time2 timestamp,
        time3 timestamp, time4 timestamp);
        insert into {self.tb_name} values(TO_TIMESTAMP('WRONG' DEFAULT '20-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '21-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '22-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '23-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'));
        insert into {self.tb_name} values(TO_TIMESTAMP('WRONG' DEFAULT '19-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '20-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '21-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '22-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'));
        insert into {self.tb_name} values(TO_TIMESTAMP('WRONG' DEFAULT '18-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '19-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '20-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '21-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'));
        insert into {self.tb_name} values(TO_TIMESTAMP('WRONG' DEFAULT '17-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '18-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '19-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '20-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'));
        '''
        sql_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_res,
                      f'执行失败：{text}')
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      f'执行失败：{text}')
        self.assertIn('INSERT 0 1', sql_res, f'执行失败：{text}')
        self.assertEqual(sql_res.count('INSERT 0 1'), 4, f'执行失败：{text}')

        text = '-----step2:结合匿名函数使用;expect:成功-----'
        self.log.info(text)
        sql = f'''
        do \$\$
        declare
          i int;
        begin
          for i in 1..10000 loop
            execute \$cmd\$ select to_timestamp(time1 default '2023-04-14 00:15:00.000000' on conversion error,'YYYY-mm-DD HH24:MI:SS.FF'), to_timestamp(time2 default '2023-04-14 00:15:00.000000' on conversion error,'YYYY-mm-DD HH24:MI:SS.FF'), to_timestamp(time3 default '2023-04-14 00:15:00.000000' on conversion error,'YYYY-mm-DD HH24:MI:SS.FF'), to_timestamp(time4 default '2023-04-14 00:15:00.000000' on conversion error,'YYYY-mm-DD HH24:MI:SS.FF') 
            from {self.tb_name}\$cmd\$;
            if i % 1000 = 0 then
              raise notice '执行第 % 次查询', i;
            end if;
          end loop;
        end \$\$;
        '''
        self.log.info(sql)
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG, res,
                      f'执行失败：{text}')

    def tearDown(self):
        text = '-----step3:清理环境; expect:成功-----'
        self.log.info(text)
        sql = f'drop table if exists {self.tb_name} cascade;'
        drop_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(drop_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_res,
                      f'执行失败：{text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
